package com.example.demo.dao;

import com.example.demo.model.City;
import com.example.demo.model.CityImage;
import org.apache.ibatis.annotations.*;

import java.util.List;

@Mapper
public interface CityDao {

    @Select("SELECT * FROM city WHERE id = #{id}")
    City findById(Long id);

    @Select("SELECT * FROM city")
    List<City> findAll();

    @Insert("INSERT INTO city (name, country, description, highlights, recommended_days, create_time, update_time) " +
            "VALUES (#{name}, #{country}, #{description}, #{highlights}, #{recommendedDays}, #{createTime}, #{updateTime})")
    @Options(useGeneratedKeys = true, keyProperty = "id")
    int insertCity(City city);

    @Update("UPDATE city SET name=#{name}, country=#{country}, description=#{description}, " +
            "highlights=#{highlights}, recommended_days=#{recommendedDays}, update_time=#{updateTime} " +
            "WHERE id=#{id}")
    int updateCity(City city);

    @Delete("DELETE FROM city WHERE id = #{id}")
    int deleteCity(Long id);

    @Select("SELECT * FROM city_image WHERE city_id = #{cityId} ORDER BY sort_order ASC, create_time ASC")
    List<CityImage> findImagesByCityId(Long cityId);

    @Insert("<script>" +
            "INSERT INTO city_image (city_id, image_url, caption, sort_order, is_primary, create_time) VALUES " +
            "<foreach collection='list' item='image' separator=','> " +
            "(#{image.cityId}, #{image.imageUrl}, #{image.caption}, #{image.sortOrder}, #{image.primary}, #{image.createTime})" +
            "</foreach>" +
            "</script>")
    int batchInsertImages(List<CityImage> images);

    @Delete("DELETE FROM city_image WHERE city_id = #{cityId}")
    int deleteImagesByCityId(Long cityId);

    @Select("SELECT * FROM city WHERE id = #{id}")
    @Results(id = "cityWithImagesMap", value = {
            @Result(property = "id", column = "id"),
            @Result(property = "name", column = "name"),
            @Result(property = "country", column = "country"),
            @Result(property = "description", column = "description"),
            @Result(property = "highlights", column = "highlights"),
            @Result(property = "recommendedDays", column = "recommended_days"),
            @Result(property = "createTime", column = "create_time"),
            @Result(property = "updateTime", column = "update_time"),
            @Result(property = "images", column = "id",
                    many = @Many(select = "com.example.demo.dao.CityDao.findImagesByCityId"))
    })
    City findCityWithImagesById(Long id);

    @Select("SELECT * FROM city ORDER BY create_time DESC")
    @ResultMap("cityWithImagesMap")
    List<City> findAllCitiesWithImages();

    @Select("SELECT * FROM city WHERE name LIKE CONCAT('%', #{name}, '%')")
    @ResultMap("cityWithImagesMap")
    List<City> findCitiesByNameWithImages(String name);

    // 新增方法：根据分类查询
    @Select("SELECT * FROM city WHERE highlights LIKE CONCAT('%', #{category}, '%')")
    @ResultMap("cityWithImagesMap")
    List<City> findByCategory(String category);

    // 新增方法：统计城市数量
    @Select("SELECT COUNT(*) FROM city")
    int count();
}